package com.alpha.dao;

import java.sql.SQLException;
import java.util.ArrayList;

import javax.naming.NamingException;

import com.alpha.bom.Answer;
import com.alpha.dao.interfaces.IAnswerDao;

public class AnswerDao extends AlphaDao implements IAnswerDao{

	@Override	
	public Answer retrieveAnswer(int answerId) throws SQLException, NamingException {
		Answer answer = null;
		this.connect();
		try {
			String select = "SELECT * FROM tbl_answer WHERE answer_id="+answerId;
			this.statement = this.connection.createStatement();
			this.resultSet = this.statement.executeQuery(select);
			while(this.resultSet.next()) {
				answerId = this.resultSet.getInt("answer_id");
				String answerText = this.resultSet.getString("answer_text");
				answer = new Answer(answerId, answerText);
			}
		} catch (SQLException e) {
			logger.error(e.getMessage());
			throw e;
		} finally {
			this.disconnect();
		}
		
		return answer;
	}

	@Override
	public ArrayList<Answer> retrieveChoices(int questionId) throws SQLException, NamingException {
		ArrayList<Answer> choices = new ArrayList<Answer>();
		this.connect();
		try {
			String select = "SELECT * FROM tbl_answer LEFT JOIN tbl_question_answer ON tbl_answer.answer_id=tbl_question_answer.answer_id WHERE question_id="+questionId;
			this.statement = this.connection.createStatement();
			this.resultSet = this.statement.executeQuery(select);
			while(this.resultSet.next()) {
				int answerId = this.resultSet.getInt("tbl_answer.answer_id");
				String answerText = this.resultSet.getString("answer_text");
				choices.add(new Answer(answerId, answerText));
			}
		} catch (SQLException e) {
			logger.error(e.getMessage());
			throw e;
		} finally {
			this.disconnect();
		}
		return choices;
	}

	@Override
	public int retrieveNextAnswerId() throws SQLException {
		int answerId = 0;
		try {
			String select = "SELECT MAX(answer_id)+1 FROM tbl_answer";
			this.statement = this.connection.createStatement();
			this.resultSet = this.statement.executeQuery(select);
			while(this.resultSet.next()) {
				answerId = this.resultSet.getInt(1);
				answerId = answerId > 0 ? answerId : 1;
			}
		} catch (SQLException e) {
			logger.error(e.getMessage());
			throw e;
		} 
		
		return answerId;
	}

	@Override
	public boolean insertAnswer(Answer answer) throws SQLException, NamingException {
		boolean success = false;
		this.connect();
		String insert = "INSERT INTO tbl_answer(answer_id, answer_text) VALUES (?,?)";
		try {
			this.preparedStatement = this.connection.prepareStatement(insert);
			this.preparedStatement.setInt(1, this.retrieveNextAnswerId());
			this.preparedStatement.setString(2, answer.getAnswer());
			int rows = this.preparedStatement.executeUpdate();
			if(rows > 0) {
				success = true;
			}
		} catch (SQLException e) {
			logger.error(e.getMessage());
			throw e;
		} finally {
			this.disconnect();
		}
		
		return success;
	}

	@Override
	public boolean insertAnswer(Answer answer, int questionId) throws SQLException, NamingException {
		boolean success = false;
		this.connect();
		String insert = "INSERT INTO tbl_answer(answer_id, answer_text) VALUES (?,?)";
		String insert2 = "INSERT INTO tbl_question_answer(answer_id, question_id) VALUES (?,?)";
		try {
			answer.setAnswerId(this.retrieveNextAnswerId());
			this.preparedStatement = this.connection.prepareStatement(insert);
			this.preparedStatement.setInt(1, answer.getAnswerId());
			this.preparedStatement.setString(2, answer.getAnswer());
			int rows = this.preparedStatement.executeUpdate();
			
			this.preparedStatement = this.connection.prepareStatement(insert2);
			this.preparedStatement.setInt(1, answer.getAnswerId());
			this.preparedStatement.setInt(2, questionId);
			rows += this.preparedStatement.executeUpdate();
			
			if(rows > 0) {
				success = true;
			}
		} catch (SQLException e) {
			logger.error(e.getMessage());
			throw e;
		} finally {
			this.disconnect();
		}
		
		return success;
	}

	@Override
	public boolean insertQuestionAnswer(int answerId, int questionId) throws SQLException, NamingException {
		boolean success = false;
		this.connect();
		String insert = "INSERT INTO tbl_question_answer(answer_id, question_id) VALUES (?,?)";
		try {
			this.preparedStatement = this.connection.prepareStatement(insert);
			this.preparedStatement.setInt(1, answerId);
			this.preparedStatement.setInt(2, questionId);
			int rows = this.preparedStatement.executeUpdate();
			
			if(rows > 0) {
				success = true;
			}
		} catch (SQLException e) {
			logger.error(e.getMessage());
			throw e;
		} finally {
			this.disconnect();
		}
		
		return success;
	}
}
